from django.http import HttpResponse
from django.shortcuts import render
from django.db import connection
from pyecharts.charts import Pie, Bar
from pyecharts.options import InitOpts, TitleOpts, LegendOpts, LabelOpts, AxisOpts, TooltipOpts


def echarts_list(request):
    return render(request,'echart/echart_list.html')


def chart1(request):
    '''制作一个饼图'''
    with connection.cursor() as cursor:
        cursor.execute("select dept.dname,dept_id,cn,cn2,round(cn/cn2*100,0) v from \
(select dept_id,count(*) cn,(select count(*) cn2 from app1_emp) cn2 from app1_emp group by dept_id) tmp \
,app1_dept as dept where tmp.dept_id = dept.deptno")
        rows = cursor.fetchall()
        rows = [(r[0],r[-1]) for r in rows]
    datas = [('开发部',23),('销售部',30),('后勤部',10),('财务部',40),]
    datas = rows
    pie = Pie()
    pie.add('',datas,radius='55%')
    pie.set_global_opts(title_opts=TitleOpts(title='部门人数',subtitle='单位（人）',pos_left='center',text_align='auto'),
                        legend_opts=LegendOpts(pos_bottom=0))
    pie.set_series_opts(label_opts=LabelOpts(formatter="{b}:{c}%"),)

    resp = HttpResponse(pie.dump_options_with_quotes(),content_type='application/json')
    return resp

def chart2(request):
    '''统计每个部门有多少人'''
    with connection.cursor() as cursor:
        cursor.execute('select d.dname,count(*) cn from app1_emp e left join app1_dept d on(e.dept_id=d.deptno) group by d.dname')
        rows = cursor.fetchall()
        dept_dict = {}
        for row in rows:
            dept_dict[row[0]] = row[1]

    #print(dept_dict,list(dept_dict.keys()),list(dept_dict.values()))
    bar = Bar().add_xaxis(list(dept_dict.keys()))\
                .add_yaxis('部门人数统计表',list(dept_dict.values()))\
                .set_global_opts(yaxis_opts=AxisOpts(name='单位（人）'))
    json_str = bar.dump_options_with_quotes()
    print('json_str=',json_str)
    return HttpResponse(json_str,content_type='application/json')

def chart3(request):
    '''制作一个柱状图'''
    with connection.cursor() as cursor:
        cursor.execute("select '<3000',count(*) cn from app1_emp where sal<3000 \
                        union \
                        select '3001-5000',count(*) cn from app1_emp where sal between 3001 and 5000 \
                        union \
                        select '5001-8000',count(*) cn from app1_emp where sal between 5001 and 8000 \
                        union \
                        select '>8000',count(*) cn from app1_emp where sal > 8000")
        rows = cursor.fetchall()
        rows = [row[-1] for row in rows]
    bar_datas = [10,20,30,40] #测试数据
    bar_datas = rows #查询数据库的数据
    bar = Bar(
    ).add_xaxis(['<=3000','3001-5000','5001-8000','>8000']).add_yaxis('公司人员工资图表',bar_datas)\
    .set_global_opts(yaxis_opts=AxisOpts(name='单位（人）',axislabel_opts=LabelOpts(formatter="{value} 人")))

    resp = HttpResponse(bar.dump_options_with_quotes(),content_type='application/json')
    return resp

def chart4(request):
    '''按照部门，再按照4个区间查询工资信息'''
    bar = Bar()\
        .add_xaxis(['部门1','部门2','部门3','部门4'])\
        .add_yaxis('<3000',[3,2,1,2])\
        .add_yaxis('3001-5000',[8,2,3,2])\
        .add_yaxis('5001-8000',[9,1,1,4])\
        .add_yaxis('>8000',[3,4,5,8])\
        .set_global_opts(yaxis_opts=AxisOpts(name='单位（人）'),tooltip_opts=TooltipOpts(formatter='{b}<br/>{a}<br/>{c}人'))
    return HttpResponse(bar.dump_options_with_quotes(),content_type='application/json')